package com.cherry.openlib.datebase.origin

import android.content.ContentValues
import android.database.Cursor
import android.os.Bundle
import android.support.v7.app.AppCompatActivity
import android.view.View
import com.cherry.openlib.R
import com.cherry.openlib.R.id.input
import com.cherry.openlib.R.id.result
import kotlinx.android.synthetic.main.activity_database_test.*
import java.lang.StringBuilder
import java.util.*
import kotlin.collections.ArrayList

/**
 * 除了“查询”，所有的数据库操作都可使用 SQL 语句
 * db.executeSQL(String sql);
db.executeSQL(String sql, Object[] bindArgs);
sql语句中使用占位符，参数2 = 实际的参数集
 *
 *
 */
class DatabaseNativeTestActivity : AppCompatActivity() {

    val database by lazy {
        val sqlHelper = TestNativeSqlHelper(this, "test_native", null, 1)
        sqlHelper.writableDatabase
    }

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_database_test)
    }

    fun queryAll(view: View) {

        val studentList = ArrayList<Student1>()


        //select * from student
        // 参数说明
        // table：要操作的表
        // columns：查询的列所有名称集
        // selection：WHERE之后的条件语句，可以使用占位符
        // groupBy：指定分组的列名
        // having指定分组条件，配合groupBy使用
        // orderBy指定排序的列名
        // limit指定分页参数
        // distinct可以指定“true”或“false”表示要不要过滤重复值

        // 所有方法将返回一个Cursor对象，代表数据集的游标

        val cursor = database.query(SqlConstant.TABLE_STUDENT, null, null, null, null, null, null)
        showQueryResult(cursor, studentList)

    }

    private fun showQueryResult(cursor: Cursor, studentList: ArrayList<Student1>) {
        cursor.moveToFirst()
        while (!cursor.isAfterLast) {
            val id = cursor.getInt(cursor.getColumnIndex("id"))
            val name = cursor.getString(cursor.getColumnIndex("name"))
            val gender = cursor.getString(cursor.getColumnIndex("gender"))
            studentList.add(Student1(id, name, gender))
            cursor.moveToNext()
        }
        cursor.close()

        if (studentList.isNotEmpty()) {
            val builder = StringBuilder()
            builder.append("id").append("\t").append("name").append("\t").append("gender").append("\n")
            studentList.forEach {
                builder.append(it.id).append("\t").append(it.name).append("\t").append(it.gender).append("\n")
            }
            result.text = builder.toString()
        } else {
            result.text = ""
        }
    }

    fun addFive(view: View) {
        //insert into
        database.beginTransaction()
        for (i in 1..5) {
            val values = ContentValues().apply {
                put("name", "王${Random().nextInt(10)}")
                put("gender", if (i % 2 == 0) "男" else "女")
            }
            database.insertOrThrow(SqlConstant.TABLE_STUDENT, null, values)
        }
        database.setTransactionSuccessful()
        database.endTransaction()
    }

    fun delete(view: View) {
        database.beginTransaction()
        if (input.text.toString().isNotEmpty()) {
            database.delete(SqlConstant.TABLE_STUDENT, "name=?", arrayOf(input.text.toString()))
        }

        database.setTransactionSuccessful()
        database.endTransaction()
    }

    fun queryWhere(view: View) {
        if (input.text.toString().isNotEmpty()) {
            val studentList = ArrayList<Student1>()
            //采用sql语句查询
            val cursor = database.rawQuery(SqlConstant.QUERY_WHERE_NAME, arrayOf(input.text.toString()))
            showQueryResult(cursor, studentList)
        }
    }

    fun update(view: View) {
        if (input.text.toString().isNotEmpty()) {
            database.execSQL(SqlConstant.UPDATE_WHERE_NAME, arrayOf(input.text.toString()))
        }
    }

    override fun onDestroy() {
        super.onDestroy()
        database.close()
    }

}
